Process Tabular Data in Variables

A common application of Macro Express I see on the forums is manipulating tabular data in an application like Excel. The user typically arrows about and copies and pastes each movement as a human would. This is fine for small quantities but when one get to larger sets some shortcomings become apparent: the navigation can get lost, range limits can be hard to detect, and most importantly the application needs to maintain focus. That last bit especially can be a pain with large data sets. A better solution is to grab all of the data at once and process it all in array variables.

The first thing you must understand is that when a clip of Excel data is copied to a MEP variable the cells are delimited by invisible tab characters and each line ends in a carriage return & line feed. You can see this by pasting into a hex editor. Read here to understand better. This is a Tab Separated Values format herein known as TSV which is very much like the CSV format you may be familiar with the exception that there are no need for text quotes and it uses tabs as a delimiter instead of commas which the text could contain. Note that most MS applications work this way.

Consider this simple practical example. This is a small set of data to keep things simple but imagine it’s thousands of rows. Suppose you get this report and need to enter it into your system which requires the SSN have hyphens and the and the first and last names need to be separate. Bear in mind I am not including any of the validation for clarity and I limit the arrays to 99 elements. Also I broke this into a couple more loops than necessary for clarity.

Voila! After running this macro you can download here you can see the result. At the bottom I’ll include the Command Text for quick reference. All I did was split selection into a temp rows array using CRLF and then split the rows into cells tab TAB giving me arrays representing the two columns. Then I manipulated each cell and save the results into 3 result arrays. Finally in a loop I joined each element of corresponding index into a temp array of rows then combined the rows into the output string. Essentially the reverse process.

Trivia: Did you notice we’re using a tab character with tab-ular data? It’s not a coincidence, “tab” is short for tabular. In the typewriter days one would set up tab stops so when you typed you could make tables of text more easily. Oddly enough this key was repurposed to move from field to field in forms hence the term ‘tabbing’. Another reason I like using tabs as a delimiter as it harkens back to the original design intent.

// Get some bits for the dialog box
Variable Set From Misc:  "Name of Current Macro" into %Macro Name%
Variable Set Integer %Active Monitor%: Set to Active Monitor
// Make special characters
Variable Set to ASCII Char 9 to %Tab% // Create a variable for the invisible Tab character
Variable Set to ASCII Char 10 to %LF% // Create a variable for the invisible Line Feed character
Variable Set to ASCII Char 13 to %CR% // Create a variable for the invisible Carriage Return character
// Get the contents of the clipboard
Text Box Display: %Macro Name% // Prompt the user to copy the cells
Variable Set String %Clip% from the clipboard contents
Variable Modify String %Clip%: Trim // Remove any leading or trailing invisible characters
// Split clipboard into arrays
Split String "%Clip%" on "%CR%%LF%" into %Temp%, starting at 1 // Populates a temporary array where each element is one of the rows
Repeat Start (Repeat 99 times) // Using a counter that will later set the repeat limits representing the total number of rows.
  If Variable %Temp[%RowCount%]% Equals "" // If the row is blank
    Variable Modify Integer %RowCount%: Decrement // Reduce the row count by one
    Break // Exit the loop
  End If
  Variable Modify String %Temp[%RowCount%]%: Trim // Remove a possible end tab
  Split String "%Temp[%RowCount%]%" on "%Tab%" into %RowTemp%, starting at 1 // Split each cell in the row into a temp array
  Variable Set String %SourceSSN[%RowCount%]% to "%RowTemp[1]%"
  Variable Set String %SourceName[%RowCount%]% to "%RowTemp[2]%"
End Repeat
// Modify values and populate results arrays
Repeat Start (Repeat %RowCount% times) // Go thru each element.
  Variable Modify String: Copy a substring in %SourceSSN[%C%]%, starting at 1 and 3 characters long to %SSN1% // Get the first chunk
  Variable Modify String: Copy a substring in %SourceSSN[%C%]%, starting at 4 and 2 characters long to %SSN2% // Get the second chunk
  Variable Modify String: Copy a substring in %SourceSSN[%C%]%, starting at 6 and 4 characters long to %SSN3% // Get the third chunk
  Variable Set String %SSN[%C%]% to "%SSN1%-%SSN2%-%SSN3%" // Combine chunks into a hyphenated form
  Split String "%SourceName[%C%]%" on ", " into %RowTemp%, starting at 1 // Split source name on comma-space
  Variable Set String %LastName[%C%]% to "%RowTemp[1]%" // Create the last name
  Variable Set String %FirstName[%C%]% to "%RowTemp[2]%" // Create the first name
End Repeat
// Combine resulting arrays for output
Repeat Start (Repeat %RowCount% times) // Go thru each element.
  Variable Set String %RowTemp[1]% to "%SSN[%C%]%" // SSN
  Variable Set String %RowTemp[2]% to "%LastName[%C%]%" // Last Name
  Variable Set String %RowTemp[3]% to "%FirstName[%C%]%" // First Name
  Join String %RowTemp%[1..3] with "%Tab%" into %Temp[%C%]% // Join the row temp array with tabs and put into the temp array
  Join String %Temp%[1..%RowCount%] with "%CR%%LF%" into %Clip% // Combine all the row elements int a single string varaible
  Variable Set String %Clip% to "SSN%Tab%Last Name%Tab%First Name%CR%%LF%%Clip%" // Add a header
  Variable Modify String: Save %Clip% to the clipboard // Put the results on the clipboard
End Repeat
// Output
Text Box Display: %Macro Name% // Instruct the user what to do once done.